Charting with multiple series

Introduction

This example demonstrates how to create the chart shown below that requires multiple series of data and datasets. 

The chart displays sales totals for individual employees as a bar chart with each color representing sales for a fiscal year.  The chart includes sales from fiscal years 2006 – 2008.  This chart does not require input selections from the user.

A series of data will be created for each fiscal year, with a dataset associated with each series.

The chart uses the same database tables and similar DataBlocks to those used in the Summing and Grouping example (found later in this document under Advanced Query Techniques – Example 8) that reports sales totals grouped by employee ID.  Please read the Summing and Grouping example before proceeding further with this example.

This image shows the bar chart that will be designed. It contains sales totals for six employees for the years 2006 through 2008.

Create the Chart Object

The Chart Wizard was demonstrated in the Argos Report Writer Guide. It is recommended that you go through that guide fist, if you have not already done so. For this example, we will be using a classic Argos chart. For information on how to create a standard chart, please refer to the Chart Wizard section of the Argos Help.

  1. Click the chart icon, then click within the Design Area to create the chart object.  Expand the object to the desired size.
  2. Double-click the object to launch the Chart Wizard. 

The Chart Wizard prior to entry of data.

Name the first series

  1. Click the green plus icon green plus icon to create a series.
  2. Change the Series name to fy2006
  3. Click the hardhat/hammer hardhat hammer icon to create a new dataset.
  4. Enter ‘fy2006’ as the name of the dataset and click OK.
  5. Select SQL Statement and click OK to launch the SQL Editor.
    The empty s q l editor
  6. Click the Build Query icon build query icon to launch the Build Query dialog box.


Creating the new dataset for the first series

Within the Build Query dialog box, create the query shown in the figure below using the Employees, Orders, Order_Details, and Products tables that were used in the Summing and Grouping example. 

The SELECT clause:

The data entered under the SELECT tab. The first column is a calculated value. The Field field is unit price times quantity. The type is float, and the As field reads total underscore sales. Summing is set to sum. The second column is simply the emp i d field from the employees table. Summing is set to group by.

The WHERE clause:

The data entered under the WHERE tab. The first column checks for sale dates that are greater than or equal to january first 2006. The second column checks for sale dates less than or equal to december 31st 2006.

Tip: The use of  the '#' delimiter before and after the date is the required syntax for MS-Access (which uses the Jet database engine).  Other databases may require a different delimiter.

This completes the query for the fy2006 dataset.  Later, queries will be developed for fy2007 and fy2008 with the only difference being in the WHERE clause where the corresponding date range will be changed.  The queries for years 2007 and 2008 will be pasted from the first query that was developed for fy2006.  After pasting, the date range shown above will be modified to reflect the corresponding fiscal year.

Click OK and validate the query and return to the Chart Wizard.

Select the Chart Types, labels, and other options

  1. On the Data tab of the Chart Wizard, Select 'total_sales' for the Value field.
  2. Select 'emp_id' for the Label field.
    The Chart Wizard with the data tab showing. The series name is f y 2006, there is no title, the data set is f y 2006, the value is total sales, and the label is emp i d.
  3. Under the Type tab, select the Bar Chart radio button and click on the “Sides” Chart Sub Type.
    The chart wizard with the type tab showing. There are a number of chart types available, including bar, pie, line, and area. Bar chart is selected. There are several chart subtypes available including, normal, sides, and stack. Sides is selected.
  4. Under the Labels tab, uncheck the Visible Label box. This removes the labels that are on the bars.
  5. Click the Next button to view the Chart Theme and Panel selections.  Choose whatever theme and palette you desire.
  6. Click the Next button and enter the titles.  This example used “Sales Total” for the vertical axis title and “Employee ID” for the horizontal axis title.
  7. Click the Finish button to complete the design of the first series.  This brings you back to the Argos DataBlock Designer.
    This image shows the report design after creation of the first dataset for fiscal year 2006.
  8. Save and Test if you desire.  

This completes the series and dataset for fiscal year 2006. When you are ready to create the chart for the next series, double-click on the chart to launch the Chart Wizard again.

Create the next series for fy2007

Before beginning the design for fy2007 you need to copy the fy2006 dataset.  

  1. Click fy2006 under “Series” and click the hardhat/hammer icon hardhat hammer icon to launch the SQL Editor.
  2. Click Edit Visual Design to launch the Build Query dialog.
  3. Click the Copy icon copy icon at the top of the dialog box.
  4. Click Close to return to the SQL Editor. Click Close again to return to the Chart Wizard.
  5. Click the green plus icon green plus icon to add a new series.
  6. Name the new series 'fy2007'.
  7. Select fy2007 from the list of series at the top of the screen. 
    This is the series list at the top of the chart wizard screen. F y 2006 and f y 2007 are listed. F y 2007 is highlighted.
  8. In the Dataset field, select “Create a new dataset…” then click the hardhat/hammer icon hardhat hammer icon. 
  9. Name the new dataset 'fy2007' and click OK.
  10. Select SQL Statement and click OK to launch the SQL Editor.
  11. Click the Build Query icon build query icon to launch the Build Query editor.
  12. Click Paste paste icon to paste the dataset created for fy2006. 
  13. Within the Build Query dialog box, click the WHERE tab and change the year in each date from 2006 to 2007. 
    The where tab of the query. The year in each date has been changed from 2006 to 2007.
  14. Click OK to exit the Build Query editor. Click OK again and validate the query to return to the Chart Wizard.
  15. Select total_sales for the Value and select emp_id for the Label.
  16. Under the Type tab, select Bar Chart and Sides for the type and sub type, respectively.
  17. Click the Labels tab and uncheck 'Visible Label'.
  18. Click Next and adjust the colors and theme as desired. The bars for the second series should be a different color to distinguish them from the first series.
    Complete chart with f y 2007 and 2006. The bars for each series are a different color.

The dataset for fy2007 is now complete.  Repeat this process for the third dataset (fy2008).

Summary

This example illustrated how to create several data series to be displayed on one chart and how to associate datasets with each series. Save this as a Dashboard. 

Note: Do not mix chart styles among series on the same chart. For instance, if series 1 is a bar chart and series 2 is a line chart, both styles will appear in the plot area, which is not desirable.
Incorrect chart style. This shows the result of setting a bar style for one series and line style for another series. The data for the line chart appears on the same chart as the bars.

Note: Be sure that the correct Series is highlighted when editing otherwise you may be unintentionally changing a Series that you didn’t want to change.